<?php
/**
 * Description
 * User: duanwenjie
 * Date: 2019/5/29
 * Time: 11:59 AM
 */

namespace app\index\service;
use app\common\ConfigBase;
use app\common\model\ToolModel;
use app\common\service\DownService;
use app\common\service\SkuService;
use think\Db;
use think\Exception;

class ShipplanfollowService
{
    static public function getList($param, $is_download = false)
    {
        try{
            $where = "where 1 = 1 ";
            $pageNumber = isset($param['page']) ? $param['page'] : 1;
            $pageData = isset($param['limit']) ? $param['limit'] : 20;
            $code = isset($param['code']) ? $param['code'] : '';  // 发运计划单号
            $sku = isset($param['sku']) ? $param['sku'] : '';  // SKU
            $offset = ($pageNumber - 1) * $pageData;
            $limit = " LIMIT $offset,$pageData";

            if (!empty($code)) {
                $code = trim($code);
                $where .= " AND SP.code = '{$code}'";
            }
            if (!empty($sku)) {
                $sku = trim($sku);
                $where .= " AND SPD.sku = '{$sku}'";
            }

            $sqlCount = "SELECT SP.code FROM ship_plan SP 
                    LEFT JOIN ship_plan_detail SPD ON SP.id = SPD.ship_plan_id 
                    LEFT JOIN ship_plan_logistics SPL ON SP.code = SPL.code 
                    LEFT JOIN ship_plan_receive SPR ON SP.code = SPR.code AND SPD.sku = SPR.sku
                    LEFT JOIN ship_plan_send SPS ON SP.code = SPS.code AND SPD.sku = SPS.sku
                    LEFT JOIN base_shop BS ON SPD.shop_id = BS.id
                    LEFT JOIN base_warehouse BW ON SP.warehouse_id_out = BW.id {$where} GROUP BY SP.`code`,SPD.sku ";
            $countArr = ToolModel::queryData($sqlCount);

            if (empty($countArr)) {
                if ($is_download) exitJson(["code" => "500", "msg" => "无有效数据"]);
                return array('total' => 0, 'list' => array());
            }
            $fileds = 'SP.code,SPL.local_order_no,BS.shop_name,SPD.sku,SPD.sku_name,SPD.shop_id,
                    SPD.demand_quantity,SPD.real_quantity,BW.warehouse_name,SPD.unit_volume,
                    SPD.total_volume,SPL.out_customs_clearance_date,SPL.reality_departure_date,
                    SPL.reality_arrival_date,SPL.in_customs_clearance_date';

            $sql = "SELECT {$fileds} FROM ship_plan SP 
                    LEFT JOIN ship_plan_detail SPD ON SP.id = SPD.ship_plan_id 
                    LEFT JOIN ship_plan_logistics SPL ON SP.code = SPL.code 
                    LEFT JOIN ship_plan_receive SPR ON SP.code = SPR.code AND SPD.sku = SPR.sku
                    LEFT JOIN ship_plan_send SPS ON SP.code = SPS.code AND SPD.sku = SPS.sku
                    LEFT JOIN base_shop BS ON SPD.shop_id = BS.id
                    LEFT JOIN base_warehouse BW ON SP.warehouse_id_out = BW.id {$where} GROUP BY SP.`code`,SPD.sku
                    ORDER BY SP.id DESC {$limit}";
            
            if ($is_download) return "SELECT {$fileds} FROM ship_plan SP 
                    LEFT JOIN ship_plan_detail SPD ON SP.id = SPD.ship_plan_id 
                    LEFT JOIN ship_plan_logistics SPL ON SP.code = SPL.code 
                    LEFT JOIN ship_plan_receive SPR ON SP.code = SPR.code AND SPD.sku = SPR.sku
                    LEFT JOIN ship_plan_send SPS ON SP.code = SPS.code AND SPD.sku = SPS.sku
                    LEFT JOIN base_shop BS ON SPD.shop_id = BS.id
                    LEFT JOIN base_warehouse BW ON SP.warehouse_id_out = BW.id {$where} GROUP BY SP.`code`,SPD.sku
                    ORDER BY SP.id DESC ";

            $list = ToolModel::queryData($sql);
            $list = self::formatData($list);
            $data = array();
            $data['total'] = count($countArr);
            $data['list'] = $list;

            return $data;
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }
    }

    /**
     * 导出数据
     * @param $param
     * @return array
     * @author duanwenjie
     */
    static public function export($param)
    {
        $sql = self::getList($param,true);
        $title = array(
            'code' => '发运计划单号',
            'local_order_no' => '发运单号(内单号)',
            'sku' => 'SKU',
            'sku_name' => 'SKU中文名称',
            'demand_quantity' => '需求数量',
            'real_quantity' => '计划发运数量',
            'warehouse_name' => '出库仓库',
            'out_quantity' => '出库数量',
            'unit_volume' => '单个体积',
            'total_volume' => '总体积',
            'out_customs_clearance_date' => '出口清关日期',
            'reality_departure_date' => '离港日期',
            'reality_arrival_date' => '抵港日期',
            'in_customs_clearance_date' => '进口清关日期',
            'in_quantity' => '到货数量'
        );

        $res = DownService::getData($sql,$title,'\app\index\service\ShipplanfollowService::formatData',$param);

        return $res;
    }


    /**
     * 整理数据获取头程签收数量
     * @param $data
     * @return mixed
     * @author duanwenjie
     */
    static public function formatData($data)
    {
        $codes = array_column($data, 'code');
        $shop_ids = array_column($data, 'shop_id');
        $skus = array_column($data, 'sku');;
        $infos = self::getCheckData($codes, $shop_ids, $skus);
        $infos_ = self::getSendCheckData($codes, $shop_ids, $skus);
        foreach ($data as &$value){
            $key = $value['code'] . "_" . $value['shop_id'] . "_" . $value['sku'];
            $value['in_quantity'] = $infos[$key] ?? 0;
            $value['out_quantity'] = $infos_[$key] ?? 0;
        }

        return $data;
    }

    /**
     * 获取头程签收数量
     * @param array $codes
     * @param array $shop_ids
     * @param array $skus
     * @return array
     * @author duanwenjie
     */
    static public function getCheckData($codes = [], $shop_ids = [], $skus = [])
    {
        $infos = Db::name('ship_plan_receive')
            ->field('code,shop_id,sku,SUM(quantity) as in_quantity')
            ->where('code','in',$codes)
            ->where('shop_id','in',$shop_ids)
            ->where('sku','in',$skus)
            ->group('code,shop_id,sku')
            ->select();
        $data_ = array();
        foreach ($infos as $value){
            $key = $value['code'] . "_" . $value['shop_id'] . "_" . $value['sku'];
            $data_[$key] = $value['in_quantity'];
        }

        return $data_;
    }

    /**
     * 获取发运计划出库数量
     * @param array $codes
     * @param array $shop_ids
     * @param array $skus
     * @return array
     * @author duanwenjie
     */
    static public function getSendCheckData($codes = [], $shop_ids = [], $skus = [])
    {
        $infos = Db::name('ship_plan_send')
            ->field('code,shop_id,sku,SUM(quantity) as out_quantity')
            ->where('code','in',$codes)
            ->where('shop_id','in',$shop_ids)
            ->where('sku','in',$skus)
            ->group('code,shop_id,sku')
            ->select();
        $data_ = array();
        foreach ($infos as $value){
            $key = $value['code'] . "_" . $value['shop_id'] . "_" . $value['sku'];
            $data_[$key] = $value['out_quantity'];
        }

        return $data_;
    }

}